////////////////////////////////////////////////////////////////////////////////
// Copyright 2005 CORE Solutions Limited
// All rights reserved.
// PROPRIETARY AND CONFIDENTIAL INFORMATION.
// Use is subject to license terms.
//
// FUNCTION ID      :   TASK_ALERT
// AUTHOR           :   Victor.zheng 
// DATE CREATE      :   2005/10/25
// DESCRIPTION      :   a DAO file for the function, TASK_ALERT
//                  :   
//                  :   
//                  :   
//
// MODIFICATION HISTORY:
// DATE         AUTHOR          VERSION NO.         COMMENT
// -----------  --------------  ------------------  -------------------
// 2005/10/25   Victor.zheng    1.0                 source is generated.
//
//
//
////////////////////////////////////////////////////////////////////////////////

package com.erp.app.login.taskAlert.dao;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.erp.app.common.ProjectConstants;
import com.erp.app.login.taskAlert.criteria.TaskAlertSrchCriteria;
import com.erp.app.login.taskAlert.pk.TaskAlertPk;
import com.erp.app.login.taskAlert.vo.TaskAlertVo;
import com.erp.framework.model.criteria.SimpleBaseSrchCriteria;
import com.erp.framework.model.dao.SimplePageEditStyleDao;
import com.erp.framework.model.vo.SimpleBaseVo;
import com.erp.framework.types.CustomBigDecimal;
import com.erp.framework.types.CustomSqlDate;

/**
* <p>A <strong>DAO</strong>(Data Access Object) abstracts and encapsulates
* all access to the persistent store.  The DAO manages the connection with the
* data source to obtain and store data.  Also, the DAO implements
* the access mechanism required to work with the data source,
* and always provides a uniform API to its clients.
*
* @author          Victor.zheng 
* @version 1.0     2005/10/25
* @since           FrameWork 1.0
*
*/
public class TaskAlertDao extends SimplePageEditStyleDao {

	/**
	* Returns the SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing sql query
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlQuery(SimpleBaseSrchCriteria obj) {

		TaskAlertSrchCriteria criteria = (TaskAlertSrchCriteria) obj;
		String whereClause = toWhereClause(criteria);
		String groupByClause = "";
		String orderByClause = "ORDER BY COMPANY_CODE, USER_GROUP, TASK_CODE";

		String sql = "SELECT COMPANY_CODE, USER_GROUP, TASK_CODE, TASK_NAME, TASK_DESC, "
			+ "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
			+ "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
			+ "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
			+ "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
			+ "FROM T_TASK_ALERT "
			+ whereClause
			+ groupByClause
			+ orderByClause;

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Returns the where clause of a SQL select statement.
	* @param         obj     the searching criteria
	* @return        a string containing the where clause
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	private String toWhereClause(TaskAlertSrchCriteria crit) {

		TaskAlertSrchCriteria criteria = (TaskAlertSrchCriteria) crit;
		StringBuffer whereClause = new StringBuffer();

		if (criteria.getSrchCompanyCode() != null) {
			whereClause.append(whereClause.length()== 0? "WHERE ": "AND ");
			whereClause.append("COMPANY_CODE = '" + criteria.getSrchCompanyCode().trim().toUpperCase()+"' ");
		}

		/*
		if (criteria.getSrchProgram() != null) {
			whereClause.append(whereClause.length() == 0 ? " WHERE " : " AND ");
			whereClause.append(" SALES_CONTRACT_NO IN ");
			whereClause.append(" (SELECT SALES_CONTRACT_NO FROM T_SALES_CONTRACT_DTL");
			if (criteria.getSrchProgram() != null) {
				whereClause.append(" WHERE PROGRAM ='" + criteria.getSrchProgram().toUpperCase().trim() + "'");
			}
			whereClause.append(")");
		}
		if (criteria.getSrchSalesContractNoFr() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("SALES_CONTRACT_NO >= '"
				+ criteria.getSrchSalesContractNoFr().toUpperCase().trim() + "' ");
		}
		if (criteria.getSrchSalesContractNoTo() != null) {
			whereClause.append(whereClause.length() == 0 ? "WHERE " : "AND ");
			whereClause.append("SALES_CONTRACT_NO <= '"
				+ criteria.getSrchSalesContractNoTo().toUpperCase().trim() + "' ");
		}
		*/

		return whereClause.toString();
	}

	/**
	* Constructs a string of SQL insert statement
	* @return        a string of SQL insert statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlInsert() {

		String sql = "INSERT INTO T_TASK_ALERT ("
			+ "COMPANY_CODE, USER_GROUP, TASK_CODE, TASK_NAME, TASK_DESC, "
			+ "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
			+ "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
			+ "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
			+ "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE) "
			+ "VALUES ("
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, ?, ?, ?, "
			+ "?, ?, SYSDATE, ?, ?"
			+ ")";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL insert or update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsertAndModify(PreparedStatement pStmt, Object obj, String opType)
		throws SQLException {

		TaskAlertVo vo = (TaskAlertVo) obj;
		int i = 1;

		pStmt.setString(i++, vo.getCompanyCode());
		pStmt.setString(i++, vo.getUserGroup());
		pStmt.setString(i++, vo.getTaskCode());
		setNullableString(pStmt, i++, vo.getTaskName());
		setNullableString(pStmt, i++, vo.getTaskDesc());
		setNullableString(pStmt, i++, vo.getStatus());
		setNullableString(pStmt, i++, vo.getPriority());
		setNullableString(pStmt, i++, vo.getCategory());
		setNullableString(pStmt, i++, vo.getRepetition());
		setNullableDate(pStmt, i++, vo.getDueOn());
		setNullableDate(pStmt, i++, vo.getAlertOn());
		setNullableDate(pStmt, i++, vo.getPostponedOn());
		setNullableDate(pStmt, i++, vo.getCompletedOn());
		setNullableBigDecimal(pStmt, i++, vo.getEstDuration());
		setNullableBigDecimal(pStmt, i++, vo.getActualDuration());
		setNullableBigDecimal(pStmt, i++, vo.getNoDaysToPostpone());
		setNullableString(pStmt, i++, vo.getActiveInd());
		setNullableDate(pStmt, i++, vo.getActiveDate());
		setNullableDate(pStmt, i++, vo.getInactiveDate());
		setNullableString(pStmt, i++, vo.getDcInd());
		setNullableDate(pStmt, i++, vo.getDcDate());
		setNullableString(pStmt, i++, vo.getCreateUser());

		if (ProjectConstants.DB_OP_UPDATE.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getCreateDate());

		setNullableString(pStmt, i++, vo.getUpdateUser());

		if (ProjectConstants.DB_OP_INSERT.equals(opType))
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());

		if(ProjectConstants.DB_OP_UPDATE.equals(opType)){
			pStmt.setString(i++, vo.getCompanyCode());
			pStmt.setString(i++, vo.getUserGroup());
			pStmt.setString(i++, vo.getTaskCode());
			setNullableTimestamp(pStmt, i++, vo.getUpdateDate());
		}
	}

	/**
	* Sets the values for a SQL insert statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForInsert(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_INSERT);
	}

	/**
	* Constructs a string of SQL update statement
	* @return        a string of SQL update statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlUpdate() {

		String sql = "UPDATE T_TASK_ALERT SET COMPANY_CODE=?, USER_GROUP=?, TASK_CODE=?, TASK_NAME=?, TASK_DESC=?, "
			+ "STATUS=?, PRIORITY=?, CATEGORY=?, REPETITION=?, DUE_ON=?, "
			+ "ALERT_ON=?, POSTPONED_ON=?, COMPLETED_ON=?, EST_DURATION=?, ACTUAL_DURATION=?, "
			+ "NO_DAYS_TO_POSTPONE=?, ACTIVE_IND=?, ACTIVE_DATE=?, INACTIVE_DATE=?, DC_IND=?, "
			+ "DC_DATE=?, CREATE_USER=?, CREATE_DATE=?, UPDATE_USER=?, UPDATE_DATE=SYSDATE "
			+ "WHERE COMPANY_CODE=? AND USER_GROUP=? AND TASK_CODE=? "
			+ "AND (UPDATE_DATE IS NULL OR UPDATE_DATE = ?) ";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL update statement.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming VO
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForModify(PreparedStatement pStmt, Object obj)
		throws SQLException {

		setValuesForInsertAndModify(pStmt, obj, ProjectConstants.DB_OP_UPDATE);
	}

    /**
    * Constructs a string of SQL update statement and set ACTIVE_IND to NO
    * @return        a string of SQL update statement
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlDelete() {
        String sql = "UPDATE T_TASK_ALERT SET ACTIVE_IND='" + GENERIC_NO + "', INACTIVE_DATE=SYSDATE, ACTIVE_DATE=NULL WHERE COMPANY_CODE=? AND USER_GROUP=? AND TASK_CODE=? ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL update statement which set the ACTIVE_IND to NO.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForDelete(PreparedStatement pStmt, Object obj)
        throws SQLException {

        TaskAlertVo vo = (TaskAlertVo) obj;
        int i = 1;

        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getUserGroup());
        pStmt.setString(i++, vo.getTaskCode());
    }

    /**
    * Constructs a string of SQL delete statement
    * @return        a string of SQL delete statement
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected String sqlPurge() {
        String sql = "DELETE FROM T_TASK_ALERT WHERE COMPANY_CODE=? AND USER_GROUP=? AND TASK_CODE=? ";

        //super.logSqlQuery(sql);
        return sql;
    }

    /**
    * Sets the values for a SQL delete statement.
    * @param         pStmt   the incoming prepared statement
    * @param         obj     the incoming VO
    *
    * @author     Victor.zheng 
    * @since      FrameWork 1.0
    */
    protected void setValuesForPurge(PreparedStatement pStmt, Object obj)
        throws SQLException {

        TaskAlertVo vo = (TaskAlertVo) obj;
        int i = 1;

        pStmt.setString(i++, vo.getCompanyCode());
        pStmt.setString(i++, vo.getUserGroup());
        pStmt.setString(i++, vo.getTaskCode());
    }

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlFind() {

		String sql = "SELECT COMPANY_CODE, USER_GROUP, TASK_CODE, TASK_NAME, TASK_DESC, "
			+ "STATUS, PRIORITY, CATEGORY, REPETITION, DUE_ON, "
			+ "ALERT_ON, POSTPONED_ON, COMPLETED_ON, EST_DURATION, ACTUAL_DURATION, "
			+ "NO_DAYS_TO_POSTPONE, ACTIVE_IND, ACTIVE_DATE, INACTIVE_DATE, DC_IND, "
			+ "DC_DATE, CREATE_USER, CREATE_DATE, UPDATE_USER, UPDATE_DATE "
			+ "FROM T_TASK_ALERT "
			+ "WHERE COMPANY_CODE=? AND USER_GROUP=? AND TASK_CODE=? "
			+ "ORDER BY COMPANY_CODE, USER_GROUP, TASK_CODE";

		//super.logSqlQuery(sql);
		return sql;
	}

	/**
	* Sets the values for a SQL select statement for finding by primary key.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForFind(PreparedStatement pStmt, Object obj)
		throws SQLException {

		TaskAlertPk pk = (TaskAlertPk) obj;
		int i = 1;

		pStmt.setString(i++, pk.getCompanyCode());
		pStmt.setString(i++, pk.getUserGroup());
		pStmt.setString(i++, pk.getTaskCode());
	}

	/**
	* Constructs a string of SQL select statement
	* @return        a string of SQL select statement
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected String sqlList() {

		return null;	}

	/**
	* Sets the values for a SQL select statement for finding child tables.
	* @param         pStmt   the incoming prepared statement
	* @param         obj     the incoming parent PK
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected void setValuesForList(PreparedStatement pStmt, Object obj)
		throws SQLException {

	}

	/**
	* Sets the values of a result set to a vo
	* @param         rs   the incoming result set
	* @return        a vo containing the result set values
	*
	* @author     Victor.zheng 
	* @since      FrameWork 1.0
	*/
	protected SimpleBaseVo getResultSetValues(ResultSet rs) throws SQLException {

		TaskAlertVo vo = new TaskAlertVo();

		vo.setCompanyCode(rs.getString("COMPANY_CODE"));
		vo.setUserGroup(rs.getString("USER_GROUP"));
		vo.setTaskCode(rs.getString("TASK_CODE"));
		vo.setTaskName(rs.getString("TASK_NAME"));
		vo.setTaskDesc(rs.getString("TASK_DESC"));
		vo.setStatus(rs.getString("STATUS"));
		vo.setPriority(rs.getString("PRIORITY"));
		vo.setCategory(rs.getString("CATEGORY"));
		vo.setRepetition(rs.getString("REPETITION"));
		vo.setDueOn(CustomSqlDate.customize(rs.getDate("DUE_ON")));
		vo.setAlertOn(CustomSqlDate.customize(rs.getDate("ALERT_ON")));
		vo.setPostponedOn(CustomSqlDate.customize(rs.getDate("POSTPONED_ON")));
		vo.setCompletedOn(CustomSqlDate.customize(rs.getDate("COMPLETED_ON")));
		vo.setEstDuration(CustomBigDecimal.customize(rs.getBigDecimal("EST_DURATION")));
		vo.setActualDuration(CustomBigDecimal.customize(rs.getBigDecimal("ACTUAL_DURATION")));
		vo.setNoDaysToPostpone(CustomBigDecimal.customize(rs.getBigDecimal("NO_DAYS_TO_POSTPONE")));
		vo.setActiveInd(rs.getString("ACTIVE_IND"));
		vo.setActiveDate(CustomSqlDate.customize(rs.getDate("ACTIVE_DATE")));
		vo.setInactiveDate(CustomSqlDate.customize(rs.getDate("INACTIVE_DATE")));
		vo.setDcInd(rs.getString("DC_IND"));
		vo.setDcDate(CustomSqlDate.customize(rs.getDate("DC_DATE")));
		vo.setCreateUser(rs.getString("CREATE_USER"));
		vo.setCreateDate(rs.getTimestamp("CREATE_DATE"));
		vo.setUpdateUser(rs.getString("UPDATE_USER"));
		vo.setUpdateDate(rs.getTimestamp("UPDATE_DATE"));

		return vo;
	}

}
